 /****************************************************************************
	
	Date: October, 2017
		
	This file:
	Collects and Organizes the relevant firm level data
	for the calibration exercise and the motivation regression.
	
	
	Output:
	- Firm_Level_$sample
	- $outputpath/bilateralexports_wide_$sample
	-  temp_totimport_$sample
	
****************************************************************************/


cd "$datapath"


*** +++ Identify siren to be kept +++ ***

use ${input}, clear

tab siren if substr(siren,9,1)=="F"
replace siren="99"+substr(siren,2,9) if substr(siren,1,1)=="P"
drop if substr(siren,9,1)=="F"
destring siren, replace

keep siren year ape
compress
do "$dopath/00_1_apetoWIOT12.do"


gsort siren year
compress
save $outputpath/temp_ref_$sample, replace
duplicates drop siren wiot12code, force
gsort siren
save $outputpath/temp_$sample, replace
use $outputpath/temp_ref_$sample, clear
gsort siren
merge siren using $outputpath/temp_$sample
erase $outputpath/temp_$sample.dta
keep if _merge==3
drop _merge
count if year==$year
count if year==$year & wiot12code=="L"
count if year==$year & wiot12code=="J"
drop if wiot12code=="L"
drop if wiot12code=="J"
* none in brn

gsort siren year
save $outputpath/temp_ref_$sample, replace
use $outputpath/temp_ref_$sample, clear
replace year = year+1
gsort siren year
compress
save $outputpath/temp_ref_plus1_$sample, replace

*** +++ Imports at the firm*year*country level +++ ***

use "$datapathother/corres_Name_iso2_iso3_ifs", clear
duplicates drop iso3, force
replace name="Germany" if iso3=="DEU"
replace iso3="ROU" if name=="Romania"
gsort iso2
save temp_, replace

use "$datapath/import_bysirenwiot12sec_$firstyearcustoms$lastyearcustoms", clear
destring siren, replace

* SECRET DATA GOES HERE.
* BASICALLY GATHERS INTO 1 unique SIREN A GROUP OF SIRENS THAT ARE IN FACT FROM SAME COMPANY.
* WE DO SO FOR FOUR COMPANIES

* do $dopath/SECRET_DATA_02.do

gcollapse (sum) import qty_import , by (siren year pays wiot)
g usup_import=import/qty_import
gsort siren pays wiot year
egen _=group(siren pays wiot)
xtset _ year
g lag_import=l.import
drop _

rename pays iso2
gsort iso2
drop if wiot12code=="." | wiot12code=="J" | wiot12code=="P" | wiot12code=="L"
merge m:1 iso2 using temp_
erase temp_.dta

drop if _merge==2
drop _merge
g wiot=0
foreach i in $wiotcountry{
	replace wiot=1 if iso3=="`i'"
}
replace iso3="ROW" if wiot!=1
replace iso2="" if wiot!=1
replace name="Rest of the World" if wiot!=1
replace year=year+1
rename wiot12code wiot12_jp
gsort siren year

merge siren year using $outputpath/temp_ref_plus1_$sample
keep if _merge==3
drop _merge
rename wiot12code wiot12_j
gsort siren year iso3 wiot12_j wiot12_jp name
replace import=import/1000
replace lag_import=lag_import/1000
gcollapse (sum) import lag_import, by(siren year iso3 wiot12_j wiot12_jp iso2 name)
drop if wiot12_jp==""
tab wiot12_jp
g iso3wiotsec=iso3+wiot12_jp
keep siren year iso3wiotsec import lag_import iso3 wiot12_jp
compress
save temp___, replace
gsort siren year
gcollapse (sum) firm_import=import lag_firm_import=lag_import , by(siren year) 
keep siren year firm_import lag_firm_import
gsort siren year
compress
save temp_totimport_$sample, replace


*** +++ Technological factors at the firm*year level +++ ***
use ${input}, clear
replace year=year+1
gsort siren year
cap drop id_f
gegen id_f=group(siren)
gsort id_f year

tab siren if substr(siren,9,1)=="F"
replace siren="99"+substr(siren,2,9) if substr(siren,1,1)=="P"
drop if substr(siren,9,1)=="F"
destring siren, replace
compress

duplicates drop
duplicates drop year siren, force
* no duplicated obs

gsort siren year
merge 1:1 siren year using temp_totimport_$sample
replace firm_import=0 if _merge==1
replace lag_firm_import=0 if _merge==1
drop _merge
gsort id_f year
xtset id_f year 
g gr_firm_import=firm_import/lag_firm_import-1
replace gr_firm_import=. if firm_import==0

rename dep codep

*keep siren codep cpost naf700 n16 n36 n114 year naf60 mfrg effec achamar achampr saltrai fraisrd fraispat catotal vabcf gr_vabcf cafranc
* keep siren codep cpost naf* year mfrg effsalm achamar achampr saltrai catotal vabcf gr_vabcf cafranc
*if "$sample"=="ficus"{
*	keep siren codep naf* n114 year naf60 mfrg effec achamar achampr saltrai catotal vabcf firm_import gr_vabcf cafranc gr_effsalm gr_caexpor gr_cafranc gr_catotal gr_vaht gr_saltrai gr_firm_inputs gr_firm_import
*}
keep siren codep ape* year mfrg effec achamar achampr saltrai catotal vabcf firm_import gr_vabcf cafranc gr_effsalm gr_caexpor gr_cafranc gr_catotal gr_vaht gr_saltrai gr_firm_inputs gr_firm_import 
*keep siren codep ape* year mfrg achamar achampr  saltrai catotal vabcf firm_import gr_vabcf gr_saltrai cafranc gr_effsalm gr_caexpor gr_cafranc gr_catotal gr_vaht   gr_firm_import

gsort siren year
merge siren year using $outputpath/temp_ref_plus1_$sample
keep if _merge==3
drop _merge
erase temp_totimport_$sample.dta
rename wiot12code wiot12_jp
drop if year>$lastyear+1
rename codep dep
gcollapse  (mean) firm_import mfrg effec catotal cafranc achamar achampr saltrai vabcf gr_* , by(siren dep ape ape2d wiot12_jp year)
gsort siren year
keep siren year wiot12_jp firm_import catotal cafranc achamar achampr saltrai vabcf gr_* 

drop if wiot12_jp==""
compress
saveold Firm_Level_$sample, replace version(13)

*** +++ Exports at the firm*year*country level +++ ***

use "$datapathother/corres_Name_iso2_iso3_ifs", clear
duplicates drop iso3, force
replace name="Germany" if iso3=="DEU"
replace iso3="ROU" if name=="Romania"
gsort iso2
save $outputpath/temp_, replace
use "$datapath/export_bysiren_$firstyearcustoms$lastyearcustoms", clear
rename pays iso2
gsort iso2

merge m:1 iso2 using $outputpath/temp_
erase $outputpath/temp_.dta
drop if _merge==2
drop _merge
g wiot=0
foreach i in $wiotcountry{
	replace wiot=1 if iso3=="`i'"
}
replace iso3="ROW" if wiot!=1
replace iso2="" if wiot!=1
replace name="Rest of the World" if wiot!=1
replace year=year+1

* do $dopath/SECRET_DATA_02.do

gcollapse (sum) export qty_export , by (siren year iso2 iso3 wiot)


gsort siren year
merge m:1 siren year using $outputpath/temp_ref_plus1_$sample
erase $outputpath/temp_ref_plus1_$sample.dta
keep if _merge==3
drop _merge
rename wiot12code wiot12_jp
gsort siren year iso3 wiot12_jp
gcollapse (sum) export, by(siren year iso3 wiot12_jp )
replace export=export/1000
drop if wiot12_jp==""
keep siren year iso3 wiot12_jp export
gsort siren year
rename iso3 Exports_To
greshape wide export, i(siren year wiot12_jp) j(Exports_To) string
compress
saveold $outputpath/bilateralexports_wide_$sample, replace version(13)


